package com.example.cloud.service.common.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.example.cloud.model.common.CommonModel;
import com.example.cloud.model.dto.FilterModel;
import com.example.cloud.model.dto.QueryModel;
import com.example.cloud.model.dto.Result;
import com.example.cloud.model.personal.AttendanceRecord;
import com.example.cloud.model.system.Organ;
import com.example.cloud.model.system.User;
import com.example.cloud.service.common.CommonService;
import com.example.cloud.utils.RequestUtil;
import com.example.cloud.utils.ResultUtil;
import org.apache.commons.lang.StringUtils;
import org.beetl.sql.core.SQLManager;
import org.beetl.sql.core.SQLReady;
import org.beetl.sql.core.engine.PageQuery;
import org.beetl.sql.core.kit.StringKit;
import org.beetl.sql.core.query.Query;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by wangtao on 2018/5/1.
 *
 * @author wangtao
 */
@Service
public class CommonServiceImpl implements CommonService {
    @Autowired
    private SQLManager sqlManager;

    /**
     * Query查询器
     * @param queryModel
     * @param clazz
     * @return
     */
    @Override
    public Result query(QueryModel queryModel, Class clazz) {
        JSONArray data = new JSONArray();
        // 单表查询工具(Query查询器)
        Query query = sqlManager.query(clazz);
        // 添加默认过滤条件
        addDefaultConditions(query, queryModel.getDefaultFilters(), clazz);
        // 条件转换
        List<FilterModel> filters = queryModel.getFilters();
        // 字典转换
        convertValue(clazz, filters);
        if ("and".equals(queryModel.getMode())) {
            for (FilterModel filter : filters) {
                // 查询参数不能为空
                if (StringUtils.isBlank(filter.getValue1()) && StringUtils.isBlank(filter.getValue2())) {
                    return ResultUtil.success(data, pageInfo(queryModel, 0));
                }
                String field = StringKit.enCodeUnderlined(filter.getField());
                switch (filter.getOperate()) {
                    case "==":
                        query.andEq(field, filter.getValue1());
                        break;
                    case "!=":
                        query.andNotEq(field, filter.getValue1());
                        break;
                    case ">":
                        query.andGreat(field, filter.getValue1());
                        break;
                    case ">=":
                        query.andGreatEq(field, filter.getValue1());
                        break;
                    case "<":
                        query.andLess(field, filter.getValue1());
                        break;
                    case "<=":
                        query.andLessEq(field, filter.getValue1());
                        break;
                    case "LIKE":
                        query.andLike(field, "%" + filter.getValue1() + "%");
                        break;
                    case "NOT LIKE":
                        query.andNotLike(field, "%" + filter.getValue1() + "%");
                        break;
                    case "IS NULL":
                        query.andIsNull(field);
                        break;
                    case "IS NOT NULL":
                        query.andIsNotNull(field);
                        break;
                    case "IN":
                        query.andIn(field, Arrays.asList(filter.getValue1().split(",")));
                        break;
                    case "NOT IN":
                        query.andNotIn(field, Arrays.asList(filter.getValue1().split(",")));
                        break;
                    case "BETWEEN ":
                        query.andBetween(field, filter.getValue1(), filter.getValue2());
                        break;
                    case "NOT BETWEEN":
                        query.andNotBetween(field, filter.getValue1(), filter.getValue2());
                        break;
                    default:
                        break;
                }
            }
        } else if ("or".equals(queryModel.getMode())) {
            Query condition = query.condition();
            for (FilterModel filter : filters) {
                // 查询参数不能为空
                if (StringUtils.isBlank(filter.getValue1()) && StringUtils.isBlank(filter.getValue2())) {
                    return ResultUtil.success(data, pageInfo(queryModel, 0));
                }
                String field = StringKit.enCodeUnderlined(filter.getField());
                switch (filter.getOperate()) {
                    case "==":
                        condition.orEq(field, filter.getValue1());
                        break;
                    case "!=":
                        condition.orNotEq(field, filter.getValue1());
                        break;
                    case ">":
                        condition.orGreat(field, filter.getValue1());
                        break;
                    case ">=":
                        condition.orGreatEq(field, filter.getValue1());
                        break;
                    case "<":
                        condition.orLess(field, filter.getValue1());
                        break;
                    case "<=":
                        condition.orLessEq(field, filter.getValue1());
                        break;
                    case "LIKE":
                        condition.orLike(field, "%" + filter.getValue1() + "%");
                        break;
                    case "NOT LIKE":
                        condition.orNotLike(field, "%" + filter.getValue1() + "%");
                        break;
                    case "IS NULL":
                        condition.orIsNull(field);
                        break;
                    case "IS NOT NULL":
                        condition.orIsNotNull(field);
                        break;
                    case "IN":
                        condition.orIn(field, Arrays.asList(filter.getValue1().split(",")));
                        break;
                    case "NOT IN":
                        condition.orNotIn(field, Arrays.asList(filter.getValue1().split(",")));
                        break;
                    case "BETWEEN ":
                        condition.orBetween(field, filter.getValue1(), filter.getValue2());
                        break;
                    case "NOT BETWEEN":
                        condition.orNotBetween(field, filter.getValue1(), filter.getValue2());
                        break;
                    default:
                        break;
                }
            }
            if (!filters.isEmpty()) {
                query.and(condition);
            }
        }
        // 查询结果总数
        StringBuilder sql = query.getSql();
        List params = query.getParams();
        int totalSize = (int)query.count();
        query.setSql(sql);
        query.addParam(params);
        // 分页(参数1:查询起始行数,参数2：从起始行数开始查询的行数)
        query.limit(queryModel.getStartLine(), queryModel.getPageSize());
        // 排序
        query.orderBy(queryModel.getOrder());
        List list = query.select();
        if (!list.isEmpty()) {
            data = JSON.parseArray(JSON.toJSONString(list));
        }
        // 返回数据对象和分页信息
        return ResultUtil.success(data, pageInfo(queryModel, totalSize));
    }

    /**
     * 添加默认过滤条件
     *
     * @param query
     * @param clazz
     * @return
     */
    public void addDefaultConditions(Query query, List<FilterModel> defaultFilters, Class clazz) {
        // 系统默认过滤
        User user = RequestUtil.getLoginUser();
        boolean exist = true;
        if (user != null) {
            // 账户过滤
            exist = existsField(clazz, "accountId");
            if (exist && user.getAccountId() != null) {
                query.andEq("account_id", user.getAccountId());
            }
            if (user.getIsAdmin() != 1) {
                // 组织机构过滤
                exist = existsField(clazz, "organId");
                if (exist) {
                    if (!user.getOrgans().isEmpty()) {
                        List<Long> organIds = new ArrayList<Long>();
                        for (Organ organ : user.getOrgans()) {
                            organIds.add(organ.getId());
                        }
                        query.andIn("organ_id", organIds);
                    } else {
                        query.andEq("id", -1);
                    }
                }
            }
        }
        exist = CommonModel.class.isAssignableFrom(clazz);
        // 删除标识
        if(exist){
            query.andEq("delete_flag", 0);
        }
        // 追加默认过滤条件
        for (FilterModel filter : defaultFilters) {
            String field = StringKit.enCodeUnderlined(filter.getField());
            switch (filter.getOperate()) {
                case "==":
                    query.andEq(field, filter.getValue1());
                    break;
                case "!=":
                    query.andNotEq(field, filter.getValue1());
                    break;
                case ">":
                    query.andGreat(field, filter.getValue1());
                    break;
                case ">=":
                    query.andGreatEq(field, filter.getValue1());
                    break;
                case "<":
                    query.andLess(field, filter.getValue1());
                    break;
                case "<=":
                    query.andLessEq(field, filter.getValue1());
                    break;
                case "LIKE":
                    query.andLike(field, "%" + filter.getValue1() + "%");
                    break;
                case "NOT LIKE":
                    query.andNotLike(field, "%" + filter.getValue1() + "%");
                    break;
                case "IS NULL":
                    query.andIsNull(field);
                    break;
                case "IS NOT NULL":
                    query.andIsNotNull(field);
                    break;
                case "IN":
                    query.andIn(field, Arrays.asList(filter.getValue1().split(",")));
                    break;
                case "NOT IN":
                    query.andNotIn(field, Arrays.asList(filter.getValue1().split(",")));
                    break;
                case "BETWEEN ":
                    query.andBetween(field, filter.getValue1(), filter.getValue2());
                    break;
                case "NOT BETWEEN":
                    query.andNotBetween(field, filter.getValue1(), filter.getValue2());
                    break;
                default:
                    break;
            }
        }
    }

    /**
     * sql查询
     * @param queryModel
     * @param clazz
     * @return
     */
    @Override
    public Result queryBySql(QueryModel queryModel, StringBuilder sql, Class clazz) {
        JSONArray data = new JSONArray();
        // 添加默认过滤条件
        addDefaultConditionsForSql(sql, queryModel.getDefaultFilters(), clazz);
        // 条件转换
        List<FilterModel> filters = queryModel.getFilters();
        // 字典转换
        convertValue(clazz, filters);
        if ("and".equals(queryModel.getMode())) {
            for (FilterModel filter : filters) {
                // 查询参数不能为空
                if (StringUtils.isBlank(filter.getValue1()) && StringUtils.isBlank(filter.getValue2())) {
                    return ResultUtil.success(data, pageInfo(queryModel, 0));
                }
                String field = StringKit.enCodeUnderlined(filter.getField());
                switch (filter.getOperate()) {
                    case "==":
                        sql.append(" and " + field + " = " + filter.getValue1());
                        break;
                    case "!=":
                        sql.append(" and " + field + " != " + filter.getValue1());
                        break;
                    case ">":
                        sql.append(" and " + field + " > " + filter.getValue1());
                        break;
                    case ">=":
                        sql.append(" and " + field + " >= " + filter.getValue1());
                        break;
                    case "<":
                        sql.append(" and " + field + " < " + filter.getValue1());
                        break;
                    case "<=":
                        sql.append(" and " + field + " <= " + filter.getValue1());
                        break;
                    case "LIKE":
                        sql.append(" and " + field + " like '%" + filter.getValue1() + "%'");
                        break;
                    case "NOT LIKE":
                        sql.append(" and " + field + " like '%" + filter.getValue1() + "%'");
                        break;
                    case "IS NULL":
                        sql.append(" and " + field + " is null");
                        break;
                    case "IS NOT NULL":
                        sql.append(" and " + field + " is not null");
                        break;
                    case "IN":
                        sql.append(" and " + field + " in(" + filter.getValue1() + ")");
                        break;
                    case "NOT IN":
                        sql.append(" and " + field + " not in(" + filter.getValue1() + ")");
                        break;
                    case "BETWEEN ":
                        sql.append(" and " + field + " between " + filter.getValue1() + " and " + filter.getValue2());
                        break;
                    case "NOT BETWEEN":
                        sql.append(" and " + field + " not between " + filter.getValue1() + " and " + filter.getValue2());
                        break;
                    default:
                        break;
                }
            }
        } else if ("or".equals(queryModel.getMode())) {
            if(!filters.isEmpty()){
                sql.append(" and (");
                int i = 1;
                for (FilterModel filter : filters) {
                    // 查询参数不能为空
                    if (StringUtils.isBlank(filter.getValue1()) && StringUtils.isBlank(filter.getValue2())) {
                        return ResultUtil.success(data, pageInfo(queryModel, 0));
                    }
                    String field = StringKit.enCodeUnderlined(filter.getField());
                    if(i == 1){
                        switch (filter.getOperate()) {
                            case "==":
                                sql.append(field + " = " + filter.getValue1());
                                break;
                            case "!=":
                                sql.append(field + " != " + filter.getValue1());
                                break;
                            case ">":
                                sql.append(field + " > " + filter.getValue1());
                                break;
                            case ">=":
                                sql.append(field + " >= " + filter.getValue1());
                                break;
                            case "<":
                                sql.append(field + " < " + filter.getValue1());
                                break;
                            case "<=":
                                sql.append(field + " <= " + filter.getValue1());
                                break;
                            case "LIKE":
                                sql.append(field + " like '%" + filter.getValue1() + "%'");
                                break;
                            case "NOT LIKE":
                                sql.append(field + " like '%" + filter.getValue1() + "%'");
                                break;
                            case "IS NULL":
                                sql.append(field + " is null");
                                break;
                            case "IS NOT NULL":
                                sql.append(field + " is not null");
                                break;
                            case "IN":
                                sql.append(field + " in(" + filter.getValue1() + ")");
                                break;
                            case "NOT IN":
                                sql.append(field + " not in(" + filter.getValue1() + ")");
                                break;
                            case "BETWEEN ":
                                sql.append(field + " between " + filter.getValue1() + " and " + filter.getValue2());
                                break;
                            case "NOT BETWEEN":
                                sql.append(field + " not between " + filter.getValue1() + " and " + filter.getValue2());
                                break;
                            default:
                                break;
                        }
                    }else{
                        switch (filter.getOperate()) {
                            case "==":
                                sql.append(" or " + field + " = " + filter.getValue1());
                                break;
                            case "!=":
                                sql.append(" or " + field + " != " + filter.getValue1());
                                break;
                            case ">":
                                sql.append(" or " + field + " > " + filter.getValue1());
                                break;
                            case ">=":
                                sql.append(" or " + field + " >= " + filter.getValue1());
                                break;
                            case "<":
                                sql.append(" or " + field + " < " + filter.getValue1());
                                break;
                            case "<=":
                                sql.append(" or " + field + " <= " + filter.getValue1());
                                break;
                            case "LIKE":
                                sql.append(" or " + field + " like '%" + filter.getValue1() + "%'");
                                break;
                            case "NOT LIKE":
                                sql.append(" or " + field + " like '%" + filter.getValue1() + "%'");
                                break;
                            case "IS NULL":
                                sql.append(" or " + field + " is null");
                                break;
                            case "IS NOT NULL":
                                sql.append(" or " + field + " is not null");
                                break;
                            case "IN":
                                sql.append(" or " + field + " in(" + filter.getValue1() + ")");
                                break;
                            case "NOT IN":
                                sql.append(" or " + field + " not in(" + filter.getValue1() + ")");
                                break;
                            case "BETWEEN ":
                                sql.append(" or " + field + " between " + filter.getValue1() + " and " + filter.getValue2());
                                break;
                            case "NOT BETWEEN":
                                sql.append(" or " + field + " not between " + filter.getValue1() + " and " + filter.getValue2());
                                break;
                            default:
                                break;
                        }
                    }
                    i++;
                }
                sql.append(")");
            }
        }
        // 查询结果总数
        List<Map> mapList = sqlManager.execute(new SQLReady(sql.toString()), Map.class);
        int totalSize = mapList.size();
        // 分页对象
        PageQuery query = new PageQuery(queryModel.getStartLine(), queryModel.getPageSize());
        PageQuery execute = sqlManager.execute(new SQLReady(sql.toString()), Map.class, query);
        List list = execute.getList();
        if(!list.isEmpty()){
            data = JSON.parseArray(JSON.toJSONString(list));
        }
        return ResultUtil.success(list, pageInfo(queryModel, totalSize));
    }

    /**
     * 添加默认过滤条件
     *
     * @param clazz
     * @return
     */
    public void addDefaultConditionsForSql(StringBuilder sql, List<FilterModel> defaultFilters, Class clazz) {
        // 系统默认过滤
        User user = RequestUtil.getLoginUser();
        if (user != null) {
            // 账户过滤
            boolean exist = existsField(clazz, "accountId");
            if (exist && user.getAccountId() != null) {
                sql.append(" and t1.account_id = " + user.getAccountId());
            }
            if (user.getIsAdmin() != 1) {
                // 组织机构过滤
                exist = existsField(clazz, "organId");
                if (exist) {
                    if (!user.getOrgans().isEmpty()) {
                        List<String> organIds = new ArrayList<>();
                        for (Organ organ : user.getOrgans()) {
                            organIds.add(String.valueOf(organ.getId()));
                        }
                        sql.append(" and t1.organ_id in(" + String.join(",", organIds) + ")");
                    } else {
                        sql.append(" and t1.id = -1");
                    }
                }
            }
        }
        // 删除标识
        sql.append(" and t1.delete_flag = 0");
        // 追加默认过滤条件
        for (FilterModel filter : defaultFilters) {
            String field = StringKit.enCodeUnderlined(filter.getField());
            switch (filter.getOperate()) {
                case "==":
                    sql.append(" and " + field + " = " + filter.getValue1());
                    break;
                case "!=":
                    sql.append(" and " + field + " != " + filter.getValue1());
                    break;
                case ">":
                    sql.append(" and " + field + " > " + filter.getValue1());
                    break;
                case ">=":
                    sql.append(" and " + field + " >= " + filter.getValue1());
                    break;
                case "<":
                    sql.append(" and " + field + " < " + filter.getValue1());
                    break;
                case "<=":
                    sql.append(" and " + field + " <= " + filter.getValue1());
                    break;
                case "LIKE":
                    sql.append(" and " + field + " like '%" + filter.getValue1() + "%'");
                    break;
                case "NOT LIKE":
                    sql.append(" and " + field + " not like '%" + filter.getValue1() + "%'");
                    break;
                case "IS NULL":
                    sql.append(" and " + field + " is null");
                    break;
                case "IS NOT NULL":
                    sql.append(" and " + field + " is not null");
                    break;
                case "IN":
                    sql.append(" and " + field + " in(" + filter.getValue1() + ")");
                    break;
                case "NOT IN":
                    sql.append(" and " + field + " not in(" + filter.getValue1() + ")");
                    break;
                case "BETWEEN ":
                    sql.append(" and " + field + " between " + filter.getValue1() + " and " + filter.getValue2());
                    break;
                case "NOT BETWEEN":
                    sql.append(" and " + field + " not between " + filter.getValue1() + " and " + filter.getValue2());
                    break;
                default:
                    break;
            }
        }
    }

    /**
     * 判断属性是否存在
     *
     * @param clazz
     * @param fieldName
     * @return
     */
    public boolean existsField(Class clazz, String fieldName) {
        boolean exist = false;
        try {
            clazz.getDeclaredField(fieldName);
            exist = true;
        } catch (NoSuchFieldException e) {
            // 找不到不处理
        }
        return exist;
    }

    /**
     * 分页信息
     *
     * @param queryModel
     * @param totalSize
     * @return
     */
    public Map<String, Object> pageInfo(QueryModel queryModel, int totalSize) {
        Map<String, Object> params = new HashMap<String, Object>(3);
        params.put("pageNumber", queryModel.getPageNumber());
        params.put("pageSize", queryModel.getPageSize());
        params.put("totalSize", totalSize);
        return params;
    }

    /**
     * 字典转换
     *
     * @param clazz
     * @param filters
     */
    public void convertValue(Class clazz, List<FilterModel> filters) {
        for (FilterModel filterModel : filters) {
            String field = filterModel.getField();
            String value = filterModel.getValue1();
            // 请假申请表
            if (clazz.isAssignableFrom(AttendanceRecord.class)) {
                if ("type".equals(field)) {
                    switch (value) {
                        case "休息":
                            value = "0";
                            break;
                        case "调休":
                            value = "1";
                            break;
                        case "事假":
                            value = "2";
                            break;
                        case "病假":
                            value = "3";
                            break;
                        case "年假":
                            value = "4";
                            break;
                        case "婚假":
                            value = "5";
                            break;
                        case "产假":
                            value = "6";
                            break;
                        case "陪产假":
                            value = "7";
                            break;
                        case "路途假":
                            value = "8";
                            break;
                        case "其他":
                            value = "9";
                            break;
                        case "外出":
                            value = "10";
                            break;
                        default:
                            break;
                    }
                } else if ("status".equals(field)) {
                    if ("待审批".equals(value)) {
                        value = "0";
                    } else if ("同意".equals(value)) {
                        value = "1";
                    } else if ("拒绝".equals(value)) {
                        value = "2";
                    } else if ("已调休".equals(value)) {
                        value = "3";
                    }
                }
            }
            filterModel.setValue1(value);
        }
    }

}
